/**
 * 
 */
package com.orange.modules.dao.school;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.bean.SchoolItemBean;
import com.orange.modules.bean.expert.ExpertTaskBean;
import com.orange.modules.entity.school.AuthSchool;

/**
 * @author gaogs
 *
 * 2016-10-16
 */
@Repository
public class AuthSchoolRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthSchoolDao authSchoolDao;
	
	/**
	 * 根据账号查询机构信息
	 * @param account
	 * @return
	 * @throws SQLException 
	 */
	public AuthSchool queryAuthSchoolInfoByAccount(String account) throws SQLException{
		String sql="  SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert" +
				" FROM t_auth_school as a WHERE a.s_account='"+account+"' ";
		AuthSchool a=dbHelper.getBean(sql, AuthSchool.class);
		return a;
	}
	
	/**
	 * 根据机构id查询机构信息
	 * @param pid
	 * @return
	 */
	public AuthSchool queryAuthSchoolInfoById(String pid)throws SQLException{
		AuthSchool a =authSchoolDao.findOne(pid);
		return a;
		
	}
	/**
	 * 根据账号查询机构信息
	 * @param account
	 * @return
	 * @throws SQLException 
	 */
	public AuthSchool queryAuthSchoolInfoByPid(String pid) throws SQLException{
		String sql="  SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert" +
				" FROM t_auth_school as a WHERE a.p_id ='"+pid+"'";
		AuthSchool a=dbHelper.getBean(sql, AuthSchool.class);
		return a;
	}
	
	/**
	 * 根据账号查询机构信息
	 * @param account
	 * @return
	 * @throws SQLException 
	 */
	public AuthSchool queryAuthSchoolInfoByAccount(String account,String major) throws SQLException{
		String sql=" SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert" +
				" FROM t_auth_school as a WHERE a.s_account='"+account+"' AND a.s_major='"+major+"'";
		AuthSchool a=dbHelper.getBean(sql, AuthSchool.class);
		return a;
	}
	
	// 更新学校数据/修改机构代码信息/修改专业信息
	public void updateAuthSchoolInfo(AuthSchool as) throws SQLException{
		authSchoolDao.save(as);
	}
	
	// 按照专业名称查询
	public Page<AuthSchool> queryAuthSchoolPageInforBymajor(Map<String,String>map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000  a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert" +
				" FROM t_auth_school as a WHERE 1=1" );
		if(StringUtils.isNotBlank(map.get("major"))){
			sql.append(" AND a.s_major = '"+map.get("major")+"'");
		}
		if(StringUtils.isNotBlank(map.get("state"))){
			sql.append(" AND a.s_state = '"+map.get("state")+"'");
		}
		if(StringUtils.isNotBlank(map.get("schoolName"))){
			sql.append(" AND a.s_school_name like '%"+map.get("schoolName")+"%'");
		}
		if(StringUtils.isNotBlank(map.get("account"))){
			sql.append(" AND a.s_account like '%"+map.get("account")+"%'");
		}
		sql.append(" ORDER BY a.s_create_time DESC ");
		Page<AuthSchool> eduCollections = dbHelper.getPage(sql.toString(), AuthSchool.class,Integer.valueOf(map.get("pageNumber")), Integer.valueOf(map.get("pageSize")),new Object[] {});
		return eduCollections;
	}
	
	// 删除
	public void deleteuthSchoolInfo(String pid) throws SQLException {
			authSchoolDao.delete(pid);
	}
	
	// 模糊查询学校名称
	public AuthSchool queryAuthSchoolInforByschoolName(String schoolName)
			throws SQLException{
			String sql=" SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert " +
					" FROM t_auth_school as a WHERE a.s_school_name like '%"+schoolName+"%'";
			AuthSchool a=dbHelper.getBean(sql, AuthSchool.class);
			return a;
		}
	
	//分页查询
	public Page<AuthSchool> queryAuthSchoolPage(Map<String, String> map)
			throws SQLException {
		StringBuilder sql = new StringBuilder(" SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert" +
					" FROM t_auth_school as a WHERE 1 = 1");
		if(StringUtils.isNotBlank(map.get("schoolName"))){
			sql.append(" AND a.s_school_name like '%"+map.get("schoolName")+"%'");
		}
		//sql.append(" ORDER BY a.f_monitor_id ASC");
		Page<AuthSchool> eduCollections = dbHelper.getPage(sql.toString(), AuthSchool.class,Integer.valueOf(map.get("pageNumber"))-1, Integer.valueOf(map.get("pageSize")),new Object[] {});
		return eduCollections;
	}
	
	
	/**
	 * 分页查询学校数据
	 * @param pageNumber
	 * @param pageSize
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthSchool> queryAuthSchoolInfoByPage(Integer pageNumber,Integer pageSize , Map<String ,String> map) throws SQLException{
		String sql=" SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert " +
				" FROM t_auth_school a WHERE 1=1 ";
		if(map.get("major")!=null && StringUtils.isNotBlank(map.get("major"))){
			sql+=" AND a.s_major = '"+map.get("major")+"'";
		}
		if(map.get("review")!=null && StringUtils.isNotBlank(map.get("review")))
			sql+=" AND a.s_review = '"+map.get("review")+"'";
		Page<AuthSchool> page = dbHelper.getPage(sql, AuthSchool.class, pageNumber-1, pageSize);
		return page;
	}

	/**
	 * 查询所有未绑定现场考察专家组的机构
	 * @return
	 */
	public List<AuthSchool> queryUnAuthSchoolList()throws SQLException{
		String sql="  SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_password as password ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert " +
				" FROM t_auth_school a WHERE a.s_fpexpert='0' ";
		List<AuthSchool> ls=dbHelper.getBeanList(sql, AuthSchool.class);
		return ls;
		
	}
	
	/**
	 * 查询所有学校id
	 */
	public AuthSchool queryAuthSchoolAllId() throws SQLException{
		String sql = "SELECT a.p_id as pid FROM t_auth_school a WHERE 1 = 1";
		AuthSchool a = dbHelper.getBean(sql, AuthSchool.class);
		return a;
		
	}
	
	/**
	 * 需要课堂观摩评价的学校列表
	 * @return
	 * @throws SQLException 
	 */
	public Page<AuthSchool> queryAuthSchoolAndObservation(String expertId,Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 b.s_school_name as schoolName ,b.s_major as major ," +
				"　b.p_id as pid ," +
				" a.s_state as groupState ,c.s_teaching_subject as techingSubject" +
				" FROM t_auth_group as a " +
				" LEFT JOIN t_auth_school as b ON a.s_school_id=b.p_id" +
				" LEFT JOIN t_auth_export_group_map as d ON d.s_group_id=a.p_id " +
				" LEFT JOIN t_auth_observation_evaluation as c ON a.s_school_id=c.s_school_id " +
				" WHERE a.s_type='1' AND a.s_state='1' AND d.s_export_id='"+expertId+"'");
		sql.append(" ORDER BY　a.s_create_time DESC ");
		Page<AuthSchool> ls = dbHelper.getPage(sql.toString(),AuthSchool.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return ls;
	}
	
	
	/**
	 * 专家分配的专业技能学校列表
	 * @return
	 * @throws SQLException 
	 */
	public Page<ExpertTaskBean> queryAuthSchoolAndTask(String expertId,Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 a.p_id as pid, c.s_school_name as schoolName ," +
				" c.s_major as major ,a.s_task_id as taskId ,d.s_name as taskName ," +
				" b.s_state groupState ,d.s_word_url as wordUrl,a.s_group_id as groupId ," +
				" d.s_type as taskType ,a.s_task_file_url as taskFileUrl " +
				" FROM t_auth_export_task_map as a " +
				" LEFT JOIN t_auth_group as b ON a.s_group_id=b.p_id " +
				" LEFT JOIN t_auth_school as c ON b.s_school_id =c.p_id" +
				" LEFT JOIN t_auth_special_task as d ON a.s_task_id =d.p_id" +
				" WHERE b.s_state='1' AND b.s_type='1'  AND a.s_type !='0' AND a.s_export_id='"+expertId+"'");
		
		if(StringUtils.isBlank(map.get("type"))){
			sql.append(" AND d.s_type !='2' ");
		}else{
			sql.append(" AND d.s_type ='2' ");
		}
		sql.append(" ORDER BY　a.s_create_time DESC ");
		Page<ExpertTaskBean> ls = dbHelper.getPage(sql.toString(),ExpertTaskBean.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return ls;
	}
	
	/**
	 * 专家分配的一级指标学校列表
	 * @return
	 * @throws SQLException 
	 */
	public Page<ExpertTaskBean> queryAuthSchoolOneIndexAndTask(String expertId,Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000  c.s_school_name as schoolName ," +
				" c.s_major as major ,a.s_task_id as taskId ,d.s_content as taskName ," +
				" b.s_state groupState ,a.s_group_id as groupId ," +
				" d.s_type as taskType  " +
				" FROM t_auth_export_task_map as a " +
				" LEFT JOIN t_auth_group as b ON a.s_group_id=b.p_id " +
				" LEFT JOIN t_auth_school as c ON b.s_school_id =c.p_id" +
				" LEFT JOIN t_auth_item as d ON a.s_task_id =d.p_id" +
				" WHERE b.s_state='1' AND b.s_type='1' AND a.s_type ='0' AND d.s_type='0' AND a.s_export_id='"+expertId+"'");
		sql.append(" ORDER BY　a.s_create_time DESC ");
		Page<ExpertTaskBean> ls = dbHelper.getPage(sql.toString(),ExpertTaskBean.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return ls;
	}
	
	
	
	
	/**
	 * 查询所有已经提交认证申请的学校
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthSchool> queryAuthSchoolListHaveSubmit() throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_type as type ,a.s_school_name as schoolName ," +
				" a.s_account as account ," +
				" a.s_state as state ," +
				" a.s_major as major ,a.s_review as review ," +
				" a.s_login_time as loginTime ," +
				" a.s_update_time as updateTime ,a.s_create_time as createTime," +
				" a.s_science as science," +
				" a.s_liberalarts as liberalarts,a.s_whole_declaration as wholedeclaration," +
				" a.s_fpexpert as fpexpert" +
				" FROM t_auth_school as a WHERE a.s_state ='1' ";
		List<AuthSchool> list=dbHelper.getBeanList(sql, AuthSchool.class);
		return list;
	}
	
	/**
	 * 材料评审--分页查询专家绑定的学校
	 */
	public Page<SchoolItemBean> queryAuthSchoolItemById(String expertId,Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 c.s_school_name as schoolName ,c.s_major as major "+
				 " FROM t_auth_item_expert_data as a"+
				 " LEFT JOIN t_auth_school as c ON a.s_school_id = c.p_id "+
				 " WHERE a.s_expert_id = '"+expertId+"'");
		Page<SchoolItemBean> page = dbHelper.getPage(sql.toString(),SchoolItemBean.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
	
	/**
	 * 材料评审--分页查询专家绑定的分项指标
	 */
	public List<SchoolItemBean> queryAuthItemByExportIdAndSchoolId(String expertId,String schoolId) throws SQLException{
		String sql = "SELECT a.s_content as content FROM t_auth_item as a LEFT　JOIN t_auth_item_expert_map as b ON a.p_id = b.s_item_id"+
				 " WHERE b.s_expert_id = ? AND b.s_school_id = ?";
		List<SchoolItemBean> list =  dbHelper.getBeanList(sql, SchoolItemBean.class, expertId, schoolId);
		return list;
		
	}
	
	/**
	 * 材料评审意见汇总--分页查询专家组绑定的机构
	 */
	public Page<AuthSchool> queryAuthSchoolByGroupId(String groupId,Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 a.p_id as pid, a.s_school_name as schoolName, a.s_major as major"+
				 " FROM t_auth_school as a LEFT JOIN t_auth_item_expert_map as b ON a.p_id = b.s_school_id"+
				 " WHERE b.s_group_id ='"+groupId+"'");
		Page<AuthSchool> page = dbHelper.getPage(sql.toString(), AuthSchool.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
	/**
	 * 材料评审--填报数据查看
	 */
	public Page<AuthSchool> queryAuthSchoolPageByExportId(String exportId ,Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 c.p_id as pid, c.s_school_name as schoolName ,c.s_major as major ,c.s_account as account "+
				 " FROM t_auth_school_expert_map as a"+
				 " LEFT JOIN t_auth_school as c ON a.s_school_id = c.p_id "+
				 " WHERE a.s_expert_id = '"+exportId+"'");
		if(StringUtils.isNotBlank(map.get("major"))){
			sql.append(" AND c.s_major = '"+map.get("major")+"'");
		}
		Page<AuthSchool> page = dbHelper.getPage(sql.toString(),AuthSchool.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
}
